﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using LanguageInfo.IDAL;
using System.Data;
using System.Data.Common;
using System.Text.RegularExpressions;
using System.Data.SqlClient;
using LanguageInfo.Data.Helper;

namespace LanguageInfo.DAL
{
    public class WordsDAL : DataBaseClass, IWordsDAL
    {
        public DataSet WordsInfo(string strInfo, int StartIndex, int EndIndex)
        {
            string sbSql = "select *,ROW_NUMBER() OVER(ORDER BY [HEADWORD] DESC) AS RowId from dbo.LI_WORDSATTRIBUTE  ";
            if (!string.IsNullOrEmpty(strInfo))
            {
                sbSql += " where HEADWORD like  @HEADWORD + '%'";
            }

            sbSql = "select * from(" + sbSql + ") TBINFO where rowid  BETWEEN {0} AND {1} order by  HEADWORD; ";
            sbSql = string.Format(sbSql, StartIndex, EndIndex);
            using (DbCommand cmd = db.GetSqlStringCommand(sbSql.ToString()))
            {
                if (!string.IsNullOrEmpty(strInfo))
                {
                    AddInParameter(cmd, "@HEADWORD", strInfo);
                }
                return db.ExecuteDataSet(cmd);
            }
        }

        public DataSet WordsInfoTop(string strInfo)
        {
            DataSet ds = new DataSet();
            if (!string.IsNullOrEmpty(strInfo))
            {
                string sbSql = "select top 20 WORDSID,HEADWORD from dbo.LI_WORDSATTRIBUTE  ";
                sbSql += " where HEADWORD like  @HEADWORD + '%'";
                sbSql = string.Format(sbSql);
                using (DbCommand cmd = db.GetSqlStringCommand(sbSql.ToString()))
                {
                    AddInParameter(cmd, "@HEADWORD", strInfo);
                    ds = db.ExecuteDataSet(cmd);
                }
            }
            return ds;
        }

        public DataSet WordsInfoById(string strId)
        {
            DataSet ds = new DataSet();
            if (!string.IsNullOrEmpty(strId))
            {
                string sbSql = "select * from dbo.LI_WORDSATTRIBUTE  ";
                sbSql += " where WORDSID=@WORDSID";
                sbSql = string.Format(sbSql);
                using (DbCommand cmd = db.GetSqlStringCommand(sbSql.ToString()))
                {
                    AddInParameter(cmd, "@WORDSID", strId);
                    ds = db.ExecuteDataSet(cmd);
                }
            }
            return ds;
        }

        public DataSet WordsInfoByWordsId(string strWordsId)
        {
            DataSet ds = new DataSet();
            if (!string.IsNullOrEmpty(strWordsId))
            {
                string sbSql = "select * from LI_WORDSATTRIBUTEINFO  ";
                sbSql += " where WordsId=@WordsId ";
                sbSql = string.Format(sbSql);
                using (DbCommand cmd = db.GetSqlStringCommand(sbSql.ToString()))
                {
                    AddInParameter(cmd, "@WordsId", strWordsId);
                    ds = db.ExecuteDataSet(cmd);
                }
            }
            return ds;
        }


        /// <summary>
        /// 批量新增词目
        /// </summary>
        /// <param name="AddTableds"></param>
        public void InsertWordInfo(DataSet AddTableds)
        {
            //连接串
            //string connectionString = OracleDbHelper.OracleConnString;
            string cmdText_Table = @"INSERT INTO LI_WORDSATTRIBUTE(HEADWORD) VALUES("
                                + "@HEADWORD)";
            AddTableds.Tables[0].TableName = "LI_WORDSATTRIBUTE";
            DbCommand cmd = db.GetSqlStringCommand(cmdText_Table);
            db.AddInParameter(cmd, "HEADWORD", DbType.String, "HEADWORD", DataRowVersion.Current);
            db.UpdateDataSet(AddTableds, AddTableds.Tables[0].TableName, cmd, null, null, UpdateBehavior.Transactional);
        }

        /// <summary>
        /// 批量更新
        /// </summary>
        /// <param name="UpdateByIdOrOrderDs">更新的DataSet</param>
        public void UpdateWordInfo(DataSet UpdateByIdOrOrderDs)
        {

            string cmdText_Table = @"update LI_WORDSATTRIBUTE set HEADWORD=@HEADWORD 
                    where WORDSID=@WORDSID";
            UpdateByIdOrOrderDs.Tables[0].TableName = "LI_WORDSATTRIBUTE";
            DbCommand cmd = db.GetSqlStringCommand(cmdText_Table);
            db.AddInParameter(cmd, "HEADWORD", DbType.String, "HEADWORD", DataRowVersion.Current);
            db.AddInParameter(cmd, "WORDSID", DbType.Int32, "WORDSID", DataRowVersion.Current);
            db.UpdateDataSet(UpdateByIdOrOrderDs, UpdateByIdOrOrderDs.Tables[0].TableName, cmd, null, null, UpdateBehavior.Transactional);
        }

        public void DelWordInfoById(int intId)
        {
            string sbSqlInfo = "DELETE from LI_WORDSATTRIBUTEINFO where WORDSID=@WORDSID ";
            sbSqlInfo = string.Format(sbSqlInfo);
            using (DbCommand cmd = db.GetSqlStringCommand(sbSqlInfo.ToString()))
            {
                AddInParameter(cmd, "@WORDSID", intId);
                db.ExecuteNonQuery(cmd);
            }

            string sbSql = "DELETE from LI_WORDSATTRIBUTE where WORDSID=@WORDSID ";
            sbSql = string.Format(sbSql);
            using (DbCommand cmd = db.GetSqlStringCommand(sbSql.ToString()))
            {
                AddInParameter(cmd, "@WORDSID", intId);
                db.ExecuteNonQuery(cmd);
            }
        }

        public void DelWordInfoByWordIdAndPrimarySense(string WordsId, string PrimarySense)
        {
            string sbSqlInfo = "DELETE from LI_WORDSATTRIBUTEINFO where WORDSID=@WORDSID and  PrimarySense=@PrimarySense";
            sbSqlInfo = string.Format(sbSqlInfo);
            using (DbCommand cmd = db.GetSqlStringCommand(sbSqlInfo.ToString()))
            {
                AddInParameter(cmd, "@WORDSID", WordsId);
                AddInParameter(cmd, "@PrimarySense", PrimarySense);
                db.ExecuteNonQuery(cmd);
            }
        }

        public DataSet WordsInfoByWordIdAndPrimarySense(string WordsId, string PrimarySense)
        {
            DataSet ds = new DataSet();
            if (!string.IsNullOrEmpty(WordsId) && !string.IsNullOrEmpty(PrimarySense))
            {
                string sbSql = "select * from LI_WORDSATTRIBUTEINFO  ";
                sbSql += " where WordsId=@WordsId and PrimarySense=@PrimarySense";
                sbSql = string.Format(sbSql);
                using (DbCommand cmd = db.GetSqlStringCommand(sbSql.ToString()))
                {
                    AddInParameter(cmd, "@WordsId", WordsId);
                    AddInParameter(cmd, "@PrimarySense", PrimarySense);
                    ds = db.ExecuteDataSet(cmd);
                }
            }
            return ds;
        }

        /// <summary>
        /// 批量删除
        /// </summary>
        /// <param name="UpdateByIdOrOrderDs">更新的DataSet</param>
        public void DelWordInfoAll(DataSet DelDs)
        {
            string cmdText_Table = @"DELETE from LI_WORDSATTRIBUTE where WORDSID=@WORDSID";
            DelDs.Tables[0].TableName = "LI_WORDSATTRIBUTE";
            DbCommand cmd = db.GetSqlStringCommand(cmdText_Table);
            db.AddInParameter(cmd, "WORDSID", DbType.Int32, "WORDSID", DataRowVersion.Current);
            db.UpdateDataSet(DelDs, DelDs.Tables[0].TableName, cmd, null, null, UpdateBehavior.Transactional);
        }

        /// <summary>
        /// 批量新增义项信息
        /// </summary>
        /// <param name="AddTableds"></param>
        public void InsertEditWordInfo(DataSet AddTableds)
        {
            string cmdText_Table = @"INSERT INTO LI_WORDSATTRIBUTEINFO(PrimarySense,
            WordsId,Annotation,
            Variety,Note,
            Semanteme,Lable,
            ConceptStru,StructSemantics,
            GrammarNote,ModelExample,
            CollocateElement, SemanticRelation,
            CulturalInformation, UserName,
            CreateTime, FreqCode,
            FreqTransACode,Synonym,
            Antonym,DownHyponym,
            UpHyponym,Homograph,
            Homonym,FamilyWords,
            Compound, LanguageChunks) 
            VALUES(@PrimarySense,@WordsId,
            @Annotation,@Variety,
            @Note,@Semanteme,
            @Lable,@ConceptStru,
            @StructSemantics,@GrammarNote,
            @ModelExample,@CollocateElement,
            @SemanticRelation,@CulturalInformation,
            @UserName,@CreateTime,
            @FreqCode,@FreqTransACode,
            @Synonym,@Antonym,
            @DownHyponym,@UpHyponym,
            @Homograph,@Homonym,
            @FamilyWords,@Compound,
            @LanguageChunks)";
            AddTableds.Tables[0].TableName = "LI_WORDSATTRIBUTEINFO";
            DbCommand cmd = db.GetSqlStringCommand(cmdText_Table);
            db.AddInParameter(cmd, "PrimarySense", DbType.String, "PrimarySense", DataRowVersion.Current);
            db.AddInParameter(cmd, "WordsId", DbType.String, "WordsId", DataRowVersion.Current);
            db.AddInParameter(cmd, "Annotation", DbType.String, "Annotation", DataRowVersion.Current);
            db.AddInParameter(cmd, "Variety", DbType.String, "Variety", DataRowVersion.Current);
            db.AddInParameter(cmd, "Note", DbType.String, "Note", DataRowVersion.Current);
            db.AddInParameter(cmd, "Semanteme", DbType.String, "Semanteme", DataRowVersion.Current);
            db.AddInParameter(cmd, "Lable", DbType.String, "Lable", DataRowVersion.Current);
            db.AddInParameter(cmd, "ConceptStru", DbType.String, "ConceptStru", DataRowVersion.Current);
            db.AddInParameter(cmd, "StructSemantics", DbType.String, "StructSemantics", DataRowVersion.Current);
            db.AddInParameter(cmd, "GrammarNote", DbType.String, "GrammarNote", DataRowVersion.Current);
            db.AddInParameter(cmd, "ModelExample", DbType.String, "ModelExample", DataRowVersion.Current);
            db.AddInParameter(cmd, "CollocateElement", DbType.String, "CollocateElement", DataRowVersion.Current);
            db.AddInParameter(cmd, "SemanticRelation", DbType.String, "SemanticRelation", DataRowVersion.Current);
            db.AddInParameter(cmd, "CulturalInformation", DbType.String, "CulturalInformation", DataRowVersion.Current);
            db.AddInParameter(cmd, "UserName", DbType.String, "UserName", DataRowVersion.Current);
            db.AddInParameter(cmd, "CreateTime", DbType.DateTime, "CreateTime", DataRowVersion.Current);
            db.AddInParameter(cmd, "FreqCode", DbType.Int32, "FreqCode", DataRowVersion.Current);
            db.AddInParameter(cmd, "FreqTransACode", DbType.Int32, "FreqTransACode", DataRowVersion.Current);
            db.AddInParameter(cmd, "Synonym", DbType.String, "Synonym", DataRowVersion.Current);
            db.AddInParameter(cmd, "Antonym", DbType.String, "Antonym", DataRowVersion.Current);
            db.AddInParameter(cmd, "DownHyponym", DbType.String, "DownHyponym", DataRowVersion.Current);
            db.AddInParameter(cmd, "UpHyponym", DbType.String, "UpHyponym", DataRowVersion.Current);
            db.AddInParameter(cmd, "Homograph", DbType.String, "Homograph", DataRowVersion.Current);
            db.AddInParameter(cmd, "Homonym", DbType.String, "Homonym", DataRowVersion.Current);
            db.AddInParameter(cmd, "FamilyWords", DbType.String, "FamilyWords", DataRowVersion.Current);
            db.AddInParameter(cmd, "Compound", DbType.String, "Compound", DataRowVersion.Current);
            db.AddInParameter(cmd, "LanguageChunks", DbType.String, "LanguageChunks", DataRowVersion.Current);
            db.UpdateDataSet(AddTableds, AddTableds.Tables[0].TableName, cmd, null, null, UpdateBehavior.Transactional);
        }

        /// <summary>
        /// 批量更新
        /// </summary>
        /// <param name="UpdateByIdOrOrderDs">更新的DataSet</param>
        public void UpdateEditWordInfo(DataSet UpdateByIdOrOrderDs)
        {

            string cmdText_Table = @"update LI_WORDSATTRIBUTEINFO set Annotation=@Annotation,Variety=@Variety,PrimarySense=@PrimarySense,
Note=@Note,Semanteme=@Semanteme,Lable=@Lable,ConceptStru=@ConceptStru,StructSemantics=@StructSemantics,GrammarNote=@GrammarNote,
ModelExample=@ModelExample,CollocateElement=@CollocateElement,SemanticRelation=@SemanticRelation
,CulturalInformation=@CulturalInformation,UserName=@UserName,CreateTime=@CreateTime,
FreqCode=@FreqCode,FreqTransACode=@FreqTransACode,Synonym=@Synonym,Antonym=@Antonym,DownHyponym=@DownHyponym,UpHyponym=@UpHyponym,
Homograph=@Homograph,Homonym=@Homonym,FamilyWords=@FamilyWords,Compound=@Compound,LanguageChunks=@LanguageChunks 
                    where WordsId=@WordsId and PrimarySense=@OldPrimarySense";
            UpdateByIdOrOrderDs.Tables[0].TableName = "LI_WORDSATTRIBUTEINFO";
            DbCommand cmd = db.GetSqlStringCommand(cmdText_Table);
            db.AddInParameter(cmd, "OldPrimarySense", DbType.String, "OldPrimarySense", DataRowVersion.Current);
            db.AddInParameter(cmd, "PrimarySense", DbType.String, "PrimarySense", DataRowVersion.Current);
            db.AddInParameter(cmd, "WordsId", DbType.String, "WordsId", DataRowVersion.Current);
            db.AddInParameter(cmd, "Annotation", DbType.String, "Annotation", DataRowVersion.Current);
            db.AddInParameter(cmd, "Variety", DbType.String, "Variety", DataRowVersion.Current);
            db.AddInParameter(cmd, "Note", DbType.String, "Note", DataRowVersion.Current);
            db.AddInParameter(cmd, "Semanteme", DbType.String, "Semanteme", DataRowVersion.Current);
            db.AddInParameter(cmd, "Lable", DbType.String, "Lable", DataRowVersion.Current);
            db.AddInParameter(cmd, "ConceptStru", DbType.String, "ConceptStru", DataRowVersion.Current);
            db.AddInParameter(cmd, "StructSemantics", DbType.String, "StructSemantics", DataRowVersion.Current);
            db.AddInParameter(cmd, "GrammarNote", DbType.String, "GrammarNote", DataRowVersion.Current);
            db.AddInParameter(cmd, "ModelExample", DbType.String, "ModelExample", DataRowVersion.Current);
            db.AddInParameter(cmd, "CollocateElement", DbType.String, "CollocateElement", DataRowVersion.Current);
            db.AddInParameter(cmd, "SemanticRelation", DbType.String, "SemanticRelation", DataRowVersion.Current);
            db.AddInParameter(cmd, "CulturalInformation", DbType.String, "CulturalInformation", DataRowVersion.Current);
            db.AddInParameter(cmd, "UserName", DbType.String, "UserName", DataRowVersion.Current);
            db.AddInParameter(cmd, "CreateTime", DbType.DateTime, "CreateTime", DataRowVersion.Current);
            db.AddInParameter(cmd, "FreqCode", DbType.Int32, "FreqCode", DataRowVersion.Current);
            db.AddInParameter(cmd, "FreqTransACode", DbType.Int32, "FreqTransACode", DataRowVersion.Current);
            db.AddInParameter(cmd, "Synonym", DbType.String, "Synonym", DataRowVersion.Current);
            db.AddInParameter(cmd, "Antonym", DbType.String, "Antonym", DataRowVersion.Current);
            db.AddInParameter(cmd, "DownHyponym", DbType.String, "DownHyponym", DataRowVersion.Current);
            db.AddInParameter(cmd, "UpHyponym", DbType.String, "UpHyponym", DataRowVersion.Current);
            db.AddInParameter(cmd, "Homograph", DbType.String, "Homograph", DataRowVersion.Current);
            db.AddInParameter(cmd, "Homonym", DbType.String, "Homonym", DataRowVersion.Current);
            db.AddInParameter(cmd, "FamilyWords", DbType.String, "FamilyWords", DataRowVersion.Current);
            db.AddInParameter(cmd, "Compound", DbType.String, "Compound", DataRowVersion.Current);
            db.AddInParameter(cmd, "LanguageChunks", DbType.String, "LanguageChunks", DataRowVersion.Current);
            
            db.UpdateDataSet(UpdateByIdOrOrderDs, UpdateByIdOrOrderDs.Tables[0].TableName, cmd, null, null, UpdateBehavior.Transactional);
        }

        public DataSet WordsInfoAttrTreeById(string strId)
        {
            DataSet ds = new DataSet();
            if (!string.IsNullOrEmpty(strId))
            {
//                string sbSql = @"select id,value as name,ParentId as _parentId,value,(case (select COUNT(*) from LI_WORDSMODEL b where b.ParentId=a.Id)
                //                                when 0 then '' else 'closed' end) as state from LI_WORDSMODEL a where ParentId=@Id and id<>'SENSES'";

                string sbSql = @"select id,value as name,ParentId as _parentId,'' as value,(case (select COUNT(*) from LI_WORDSMODEL b where b.ParentId=a.Id)
                                                when 0 then 0 else 1 end) as state from LI_WORDSMODEL a where ParentId=@Id";
                sbSql = string.Format(sbSql);
                using (DbCommand cmd = db.GetSqlStringCommand(sbSql.ToString()))
                {
                    AddInParameter(cmd, "@Id", strId);
                    ds = db.ExecuteDataSet(cmd);
                }
            }
            return ds;
        }

        public DataSet WordsInfoAttrInfoById(string strId)
        {
            DataSet ds = new DataSet();
            if (!string.IsNullOrEmpty(strId))
            {
                string sbSql = @"select * from dbo.LI_WORDSATTR where WordsId=@Id";
                sbSql = string.Format(sbSql);
                using (DbCommand cmd = db.GetSqlStringCommand(sbSql.ToString()))
                {
                    AddInParameter(cmd, "@Id", strId);
                    ds = db.ExecuteDataSet(cmd);
                }
            }
            return ds;
        }

        public DataSet WordsInfoSensesTreeById(string strId)
        {
            DataSet ds = new DataSet();
            if (!string.IsNullOrEmpty(strId))
            {
                string sbSql = @"select id,value as name,ParentId as _parentId,(case (select COUNT(*) from LI_WORDSMODEL b where b.ParentId=a.Id)
                                when 0 then '' else 'closed' end) as state from LI_WORDSMODEL a where ParentId=@Id ";
                if (strId == "0")
                {
                    sbSql += "and id='SENSES'";
                }
                sbSql = string.Format(sbSql);
                using (DbCommand cmd = db.GetSqlStringCommand(sbSql.ToString()))
                {
                    AddInParameter(cmd, "@Id", strId);
                    ds = db.ExecuteDataSet(cmd);
                }
            }
            return ds;
        }
    }
}
